A star schema is the simplest style of data mart schema. Its name derives from its shape, with one or more fact tables referencing multiple dimension tables that surround each fact table or set of tables to make up the rays of a “star.” A fact table typically holds measurable, quantitative data, while a dimension table holds attributes related to that data. Fact tables are typically designed to a level of uniform detail, referred to as “granularity” or “grain.”
Online transaction processing (OLTP) is a class of information systems that facilitate and manage transaction-oriented applications. The term “transaction” collectively includes various types of transactions, such as: (i) transactions in the context of a computer or database; (ii) transactions in the context of business or commercial activity; and (iii) transactions based upon a system's response (usually an immediate response) to user requests. An OLTP system may span multiple networks and multiple companies, and may rely on sophisticated transaction management software, database optimization tactics, and distributed processing for efficient operation. OLTP involves gathering input, processing the input, and updating existing information to reflect that input.
Structured Query Language (SQL) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS). SQL includes provisions for data insert, query, update, and delete, schema creation and modification, and data access control. It is a standard of the American National Standards Institute (ANSI) and the International Organization for Standards (ISO), though features and compatibility can vary among suppliers.
A materialized view (also referred to herein as a materialized query table, or MQT) is a database object that stores the results of a query. An MQT may have one, or more, of the following characteristics or features: (i) a subset or summary of the data store to which the query was directed (herein sometimes referred to as a “queried database” or “base tables”); (ii) stored in physical and/or logical proximity to the queried database; (iii) updated, from time to time, as the underlying data of the queried database changes. Materialized views are known to be useful in data warehousing scenarios, where frequent queries against the queried database can lead to the inefficiency of repetitive query processing.
Classic data warehouse reporting is based on a dimensional fact model (DFM) design in the form of a star schema, with a central fact table containing numerical measures and satellite dimension tables containing character data related to the fact table's “facts.” The dimensions, generally de-normalized structures, are considered best for reporting, because this reduces the number of joins required. However, a classic data warehouse design normally requires extract, transform and load (ETL) processes and generally contains temporal metrics (that is, time-based metrics), but does not contain up-to-date information.